package com.study.dao.impl;

import com.study.dao.CustomerDao;
import com.study.model.Customer;
import com.study.util.JDBCUtils;
import com.study.util.PageHelper;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

/**
 * @author WS
 */
public class CustomerDaoImpl implements CustomerDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public Customer login(String username, String password) {
        try {
            String sql = "select * from customer where name = ? and pwd = ?";
            Customer user = template.queryForObject(sql, new BeanPropertyRowMapper<Customer>(Customer.class), username, password);
            return user;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

        @Override
        public PageHelper<Customer> queryAllByPagerHelper(Customer query, int i, int i1,String orderBy) {
            PageHelper<Customer> sel=new PageHelper<>();
            try {
                String sql = "select * from customer " +
                        "where name like ? and nickname like ? " +
                        "and  card_no like ? " +
                        "and card_type like ? ORDER BY "+orderBy+" limit ?,?";
                List<Customer> res = template.query(sql,new BeanPropertyRowMapper<Customer>(Customer.class),
                        "%"+(query.getName()==null?"":query.getName())+"%", "%"+(query.getNickname()==null?"":query.getNickname())+"%"
                        , "%"+(query.getCardNo()==null?"":query.getCardNo())+"%","%"+(query.getCardType()==null?"":query.getCardType())+"%",(i>0?i-1:0)*i1,i1);
                sel.setList(res);
                sql="select count(1) from customer " +
                        " where name like ? and nickname like ?" +
                        " and card_no like ?" +
                        " and card_type like ?";
                sel.setTotal(template.queryForObject(sql,Integer.class,
                        "%"+(query.getName()==null?"":query.getName())+"%", "%"+(query.getNickname()==null?"":query.getNickname())+"%"
                        , "%"+(query.getCardNo()==null?"":query.getCardNo())+"%","%"+(query.getCardType()==null?"":query.getCardType())+"%"));
                sel.setCurPage(i);
                sel.setPageSize(i1);
                sel.setOrderBy(orderBy);
                return sel;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }

        @Override
        public Customer findById(int id) {
            try {
                String sql = "select * from customer where id = ?";
                Customer user = template.queryForObject(sql, new BeanPropertyRowMapper<Customer>(Customer.class), id);
                return user;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }

    @Override
    public void update(Customer update) {
        String sql = "UPDATE customer SET name=? ,pwd=?,nickname=?,sex=?,birthday=?,work_unit=?,card_no=?,tel=?,address=?,card_type=? WHERE id=?";
        int i = template.update(sql, update.getName(),update.getPwd(),update.getNickname(),update.getSex(),update.getBirthday(),update.getWorkUnit(),update.getCardNo(),update.getTel(),update.getAddress(),update.getCardType(),update.getId());
        System.out.println("更改影响行数: " + i);
    }

    @Override
    public void deleteById(int id) {
        String sql = "DELETE FROM customer WHERE id=?";
        int i = template.update(sql,id);
        System.out.println("删除影响行数: " + i);
    }

    @Override
    public void add(Customer update) {
        String sql = "INSERT INTO customer(name,pwd,nickname,sex,birthday,work_unit,card_no,tel,address,card_type) values(?,?,?,?,?,?,?,?,?,?)";
        int i = template.update(sql, update.getName(),update.getPwd(),update.getNickname(),update.getSex(),update.getBirthday(),update.getWorkUnit(),update.getCardNo(),update.getTel(),update.getAddress(),update.getCardType());
        System.out.println("增加影响行数: " + i);
    }
}
